﻿/*=============================================================================== 
 * author:csyingwu
 * date:2012/9/28
 * function:db interface
 * 
 */
//============================================================================
using System;
using System.Linq;
using System.Text;
using Kaidou.Model;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using Kaidou.Util;

/**
 * 組合員本支店マスタパーシスター
 *
 */
namespace Kaidou.Persister
{
    public class MST_UnionPersister : Common
    {
        /**
	     * 組合員本支店マスタを登録する
	     * @param M データモデル
	     */
        public void insertMasterUnion(MstUnion M)
        {
            SqlDB db = new SqlDB();
            try
            {
                LogManager.info("MST_UnionPersister - insertMasterUnion");

                StringBuilder sql = new StringBuilder();
                sql.Append("insert into mst_union ( ");
                sql.Append("union_code, ");
                //sql.Append("branch_code, ");
                sql.Append("office_name, ");
                sql.Append("office_kana, ");
                sql.Append("office_zip_no, ");
                sql.Append("office_pref_code, ");
                sql.Append("office_city, ");
                sql.Append("office_address, ");
                sql.Append("office_tel, ");
                sql.Append("office_fax, ");
                sql.Append("office_email, ");
                sql.Append("daihyo_name, ");
                sql.Append("daihyo_kana, ");
                sql.Append("tanto_name, ");
                sql.Append("tanto_kana, ");
                sql.Append("trader_reg_no, ");
                sql.Append("user_clss, ");
                sql.Append("password, ");
                sql.Append("cancel_rest_flg, ");
                sql.Append("union_sts, ");
                sql.Append("reg_date, ");
                sql.Append("upd_date, ");
                sql.Append("upd_union_code, ");
                sql.Append("upd_branch_code, ");
                sql.Append("upd_func_id ");
                sql.Append(") values ( ");
                sql.Append("@union_code, ");
                //sql.Append("@branch_code, ");
                sql.Append("@office_name, ");
                sql.Append("@office_kana, ");
                sql.Append("@office_zip_no, ");
                sql.Append("@office_pref_code, ");
                sql.Append("@office_city, ");
                sql.Append("@office_address, ");
                sql.Append("@office_tel, ");
                sql.Append("@office_fax, ");
                sql.Append("@office_email, ");
                sql.Append("@daihyo_name, ");
                sql.Append("@daihyo_kana, ");
                sql.Append("@tanto_name, ");
                sql.Append("@tanto_kana, ");
                sql.Append("@trader_reg_no, ");
                sql.Append("@user_clss, ");
                sql.Append("@password, ");
                sql.Append("@cancel_rest_flg, ");
                sql.Append("@union_sts, ");
                sql.Append("@reg_date, ");
                sql.Append("@upd_date, ");
                sql.Append("@upd_union_code, ");
                sql.Append("@upd_branch_code, ");
                sql.Append("@upd_func_id )");


                SqlParameter[] sqlParameter = {   
                                                new SqlParameter("@union_code",SqlDbType.VarChar,5),
                                                //new SqlParameter("@branch_code",SqlDbType.SmallInt),
                                                new SqlParameter("@office_name",SqlDbType.VarChar,128),
                                                new SqlParameter("@office_kana",SqlDbType.VarChar,256),
                                                new SqlParameter("@office_zip_no",SqlDbType.VarChar,7),
                                                new SqlParameter("@office_pref_code",SqlDbType.SmallInt),
                                                new SqlParameter("@office_city",SqlDbType.VarChar,256),
                                                new SqlParameter("@office_address",SqlDbType.VarChar,256),
                                                new SqlParameter("@office_tel",SqlDbType.VarChar,11),
                                                new SqlParameter("@office_fax",SqlDbType.VarChar,11),
                                                new SqlParameter("@office_email",SqlDbType.VarChar,80),
                                                new SqlParameter("@daihyo_name",SqlDbType.VarChar,128),
                                                new SqlParameter("@daihyo_kana",SqlDbType.VarChar,128),
                                                new SqlParameter("@tanto_name",SqlDbType.VarChar,128),
                                                new SqlParameter("@tanto_kana",SqlDbType.VarChar,128),
                                                new SqlParameter("@trader_reg_no",SqlDbType.VarChar,8),
                                                new SqlParameter("@user_clss",SqlDbType.VarChar,2),
                                                new SqlParameter("@password",SqlDbType.VarChar,10),
                                                new SqlParameter("@cancel_rest_flg",SqlDbType.VarChar,1),
                                                new SqlParameter("@union_sts",SqlDbType.VarChar,2),
                                                new SqlParameter("@reg_date",SqlDbType.DateTime),
                                                new SqlParameter("@upd_date",SqlDbType.DateTime),
                                                new SqlParameter("@upd_union_code",SqlDbType.VarChar,5),
                                                new SqlParameter("@upd_branch_code",SqlDbType.SmallInt),
                                                new SqlParameter("@upd_func_id",SqlDbType.VarChar,20),
                                              };

                sqlParameter[0].Value = M.Id.UnionCode;
                //sqlParameter[1].Value = M.Id.BranchCode;
                sqlParameter[1].Value = M.OfficeName;
                sqlParameter[2].Value = M.OfficeKana;
                sqlParameter[3].Value = M.OfficeZipNo;
                sqlParameter[4].Value = M.OfficePrefCode;
                sqlParameter[5].Value = M.OfficeCity;
                sqlParameter[6].Value = M.OfficeAddress;
                sqlParameter[7].Value = M.OfficeTel;
                sqlParameter[8].Value = M.OfficeFax;
                sqlParameter[9].Value = M.OfficeEmail;
                sqlParameter[10].Value = M.DaihyoName;
                sqlParameter[11].Value = M.DaihyoKana;
                sqlParameter[12].Value = M.TantoName;
                sqlParameter[13].Value = M.TantoKana;
                if (StringUtil.isEmpty(M.TraderRegNo))
                {
                    sqlParameter[14].Value = "";
                }
                else
                {
                    sqlParameter[14].Value = M.TraderRegNo.Substring(0, 2) + "-" + M.TraderRegNo.Substring(2, 5);
                }
                sqlParameter[15].Value = M.UserClss;
                sqlParameter[16].Value = M.Password;
                sqlParameter[17].Value = M.CancelRestFlg;
                sqlParameter[18].Value = M.UnionSts;
                sqlParameter[19].Value = M.RegDate;
                sqlParameter[20].Value = M.UpdDate;
                sqlParameter[21].Value = M.UpdUnionCode;
                sqlParameter[22].Value = M.UpdBranchCode;
                sqlParameter[23].Value = M.UpdFuncId;

                db.ExecuteCmd(sql.ToString(), sqlParameter);


            }
            catch (Exception e)
            {
                LogManager.error("MST_UnionPersister - insertMasterUnion " + e.Message);
            }
            finally
            {
                db.Close();
            }
            
        }

        /**
	    * 指定された組合員のデータモデルを取得する。
	    * @param M 指定された組合員のデータモデル
        * @param pageNo ペ—ジ
        * @param pageSize
	    * @return Pager
	    */
        public Pager search(MstUnion M,int pageNo,int pageSize)
        {

            ArrayList list = new ArrayList();

            StringBuilder where = new StringBuilder();
            where.Append("select * ");

            where.Append("from mst_union ");

            where.Append(" where 1=1 ");

            if (M.Id.UnionCode != null)
            {
                where.Append(" and union_code='" + M.Id.UnionCode + "'");
                
            }
            if (M.OfficeName != null)
            {
                where.Append(" and office_name like '%" + M.OfficeName + "%'");
               
            }
            if (M.OfficeKana != null)
            {
                where.Append(" and office_kana like '%" + M.OfficeKana + "%'");
                
            }
            if (M.OfficePrefCode != 0)
            {
                where.Append(" and office_pref_code = " + M.OfficePrefCode);
                
            }
           
            if (M.TraderRegNo1 != null && M.TraderRegNo2 == null)
            {
                where.Append(" and left(trader_reg_no,2) ='" + M.TraderRegNo1 + "'");
            }

            if (M.TraderRegNo1 == null && M.TraderRegNo2 != null)
            {
                where.Append(" and right(trader_reg_no,5) ='" + M.TraderRegNo2 + "'");
            }

            if (M.TraderRegNo1 != null && M.TraderRegNo2 != null)
            {
                where.Append(" and trader_reg_no = '" + M.TraderRegNo1+"-"+M.TraderRegNo2 + "'");

            }

            where.Append(" and union_sts!='" +StringUtil.STATUS_DELETE + "'");

            where.Append(" order by office_kana asc ");
            /*
            sql.Append(" (select TOP " + pageNo * pageSize + " *  from mst_union " +where.ToString()+" order by union_code asc ) T where T.union_code not in " +
                "(select TOP " + (pageNo - 1) * pageSize + " union_code from mst_union " + where.ToString() + " order by union_code asc ) order by T.office_kana asc");
            */
            SqlParameter[] sqlParameter = { };

            LogManager.info(where.ToString());

            SqlDB db = new SqlDB();

            try
            {

                SqlDataReader dataReader = db.GetDataCmd(where.ToString(), sqlParameter);

                while (dataReader.Read())
                {
                    MstUnion model = new MstUnion();

                    MstUnionId MID = new MstUnionId();
                    model.Id = MID;

                    model.Id.UnionCode = (string)dataReader["union_code"];

                    //model.Id.BranchCode = (short)dataReader["branch_code"];

                    model.OfficeName = (string)dataReader["office_name"];

                    model.OfficeKana = (string)dataReader["office_kana"];

                    model.OfficeZipNo = (string)dataReader["office_zip_no"];

                    model.OfficePrefCode = (short)dataReader["office_pref_code"];

                    model.OfficeCity = (string)dataReader["office_city"];

                    model.OfficeAddress = (string)dataReader["office_address"];

                    model.OfficeTel = (string)dataReader["office_tel"];

                    model.OfficeFax = (string)dataReader["office_fax"];

                    model.OfficeEmail = (string)dataReader["office_email"];

                    model.DaihyoName = (string)dataReader["daihyo_name"];

                    model.DaihyoKana = (string)dataReader["daihyo_kana"];

                    model.TantoName = (string)dataReader["tanto_name"];

                    model.TantoKana = (string)dataReader["tanto_kana"];

                    model.TraderRegNo = (string)dataReader["trader_reg_no"];
                    model.TraderRegNo = model.TraderRegNo.Replace("-", "");

                    model.UserClss = (string)dataReader["user_clss"];

                    model.Password = (string)dataReader["password"];

                    model.CancelRestFlg = (string)dataReader["cancel_rest_flg"];

                    model.UnionSts = (string)dataReader["union_sts"];

                    model.RegDate = (DateTime)dataReader["reg_date"];

                    model.UpdDate = (DateTime)dataReader["upd_date"];

                    model.UpdUnionCode = (string)dataReader["upd_union_code"];

                    model.UpdBranchCode = (short)dataReader["upd_branch_code"];

                    model.UpdFuncId = (string)dataReader["upd_func_id"];

                    list.Add(model);
                }


            }
            catch (Exception exx)
            {
                LogManager.error("MST_UnionPersister - search " + exx.Message);
            }
            finally
            {
                db.Close();
            }

            if (pageNo == 0)
            {
                pageNo = 1;
            }
            Pager pager = new Pager();
            pager.PageNo = pageNo;
            pager.PageSize = pageSize;
            pager.Total = getCount(where.ToString());
            pager.Pages = pager.Total % pageSize == 0 ? pager.Total / pageSize : pager.Total / pageSize + 1;
            ArrayList result = new ArrayList();
            object[] bsm = list.ToArray();
            for (int i = (pageNo - 1) * pageSize; i < ((((pageNo - 1) * pageSize + pageSize)) >= bsm.Length ? bsm.Length : ((pageNo - 1) * pageSize + pageSize)); i++)
            {
                result.Add((MstUnion)bsm[i]);

            }
            pager.List = result;

            return pager;
        }

        /**
	     * 組合員マスタを削除する
	     * @param M データモデル
	     */
        public void deleteMasterUnion(MstUnion M)
        {
            LogManager.info("MST_UnionPersister - deleteMasterUnion - in ");
            LogManager.info("MST_UnionPersister - deleteMasterUnion - branch_code : " + M.Id.BranchCode);
            SqlDB db = new SqlDB();
            try
            {
                String pssql = "update mst_union set " + "union_sts = @union_sts, " + "upd_date = @upd_date, " + "upd_union_code = @upd_union_code, " + "upd_branch_code = @upd_branch_code " + "where " + "union_code = @union_code";



                SqlParameter[] sqlParameter = {   new SqlParameter("@union_sts", SqlDbType.VarChar, 2),
                                                  new SqlParameter("@upd_date", SqlDbType.DateTime), 
                                                  new SqlParameter("@upd_union_code", SqlDbType.VarChar, 5),
                                                  new SqlParameter("@upd_branch_code", SqlDbType.SmallInt),
                                                  new SqlParameter("@union_code", SqlDbType.VarChar, 5)
                                                 
                                              };

                sqlParameter[0].Value = M.UnionSts;
                sqlParameter[1].Value = M.UpdDate;
                sqlParameter[2].Value = M.UpdUnionCode;
                sqlParameter[3].Value = M.UpdBranchCode;
                sqlParameter[4].Value = M.Id.UnionCode;
                db.ExecuteCmd(pssql, sqlParameter);


            }
            catch (Exception e)
            {
                LogManager.error("MST_UnionPersister - deleteMasterUnion " + e.Message);
            }
            finally
            {
                db.Close();
            }
            
        }

        /**
	     * 組合員本支店マスタを更新する
	     * @param M データモデル
	     */
        public void updateMasterUnion(MstUnion M)
        {
            LogManager.info("MST_UnionPersister - updateMasterUnion");
            SqlDB db = new SqlDB();
            try
            {
                //update(M);
                String sql =
            "update mst_union set "
                + "office_name = @office_name, "
                + "office_kana = @office_kana, "
                + "office_zip_no = @office_zip_no, "
                + "office_pref_code = @office_pref_code, "
                + "office_city = @office_city, "
                + "office_address = @office_address, "
                + "office_tel = @office_tel, "
                + "office_fax = @office_fax, "
                + "office_email = @office_email, "
                + "daihyo_name = @daihyo_name, "
                + "daihyo_kana = @daihyo_kana, "
                + "tanto_name = @tanto_name, "
                + "tanto_kana = @tanto_kana, "
                + "trader_reg_no = @trader_reg_no, "
                + "user_clss = @user_clss, "
                + "password = @password, "
                + "cancel_rest_flg = @cancel_rest_flg, "
                + "union_sts = @union_sts, "
                + "reg_date = @reg_date, "
                + "upd_date = @upd_date, "
                + "upd_union_code = @upd_union_code, "
                + "upd_branch_code = @upd_branch_code, "
                + "upd_func_id = @upd_func_id "
                + "where "
                + "union_code = @union_code";


                SqlParameter[] sqlParameter = {   
                                                new SqlParameter("@union_code",SqlDbType.VarChar,5),
                                                new SqlParameter("@office_name",SqlDbType.VarChar,128),
                                                new SqlParameter("@office_kana",SqlDbType.VarChar,256),
                                                new SqlParameter("@office_zip_no",SqlDbType.VarChar,7),
                                                new SqlParameter("@office_pref_code",SqlDbType.SmallInt),
                                                new SqlParameter("@office_city",SqlDbType.VarChar,256),
                                                new SqlParameter("@office_address",SqlDbType.VarChar,256),
                                                new SqlParameter("@office_tel",SqlDbType.VarChar,11),
                                                new SqlParameter("@office_fax",SqlDbType.VarChar,11),
                                                new SqlParameter("@office_email",SqlDbType.VarChar,80),
                                                new SqlParameter("@daihyo_name",SqlDbType.VarChar,128),
                                                new SqlParameter("@daihyo_kana",SqlDbType.VarChar,128),
                                                new SqlParameter("@tanto_name",SqlDbType.VarChar,128),
                                                new SqlParameter("@tanto_kana",SqlDbType.VarChar,128),
                                                new SqlParameter("@trader_reg_no",SqlDbType.VarChar,8),
                                                new SqlParameter("@user_clss",SqlDbType.VarChar,2),
                                                new SqlParameter("@password",SqlDbType.VarChar,10),
                                                new SqlParameter("@cancel_rest_flg",SqlDbType.VarChar,1),
                                                new SqlParameter("@union_sts",SqlDbType.VarChar,2),
                                                new SqlParameter("@reg_date",SqlDbType.DateTime),
                                                new SqlParameter("@upd_date",SqlDbType.DateTime),
                                                new SqlParameter("@upd_union_code",SqlDbType.VarChar,5),
                                                new SqlParameter("@upd_branch_code",SqlDbType.SmallInt),
                                                new SqlParameter("@upd_func_id",SqlDbType.VarChar,20),
                                              };

                sqlParameter[0].Value = M.Id.UnionCode;
                sqlParameter[1].Value = M.OfficeName;
                sqlParameter[2].Value = M.OfficeKana;
                sqlParameter[3].Value = M.OfficeZipNo;
                sqlParameter[4].Value = M.OfficePrefCode;
                sqlParameter[5].Value = M.OfficeCity;
                sqlParameter[6].Value = M.OfficeAddress;
                sqlParameter[7].Value = M.OfficeTel;
                sqlParameter[8].Value = M.OfficeFax;
                sqlParameter[9].Value = M.OfficeEmail;
                sqlParameter[10].Value = M.DaihyoName;
                sqlParameter[11].Value = M.DaihyoKana;
                sqlParameter[12].Value = M.TantoName;
                sqlParameter[13].Value = M.TantoKana;
                if (StringUtil.isEmpty(M.TraderRegNo))
                {
                    sqlParameter[14].Value = "";
                }else{
                    sqlParameter[14].Value = M.TraderRegNo.Substring(0, 2) + "-" + M.TraderRegNo.Substring(2, 5);
                }
                sqlParameter[15].Value = M.UserClss;
                sqlParameter[16].Value = M.Password;
                sqlParameter[17].Value = M.CancelRestFlg;
                sqlParameter[18].Value = M.UnionSts;
                sqlParameter[19].Value = M.RegDate;
                sqlParameter[20].Value = M.UpdDate;
                sqlParameter[21].Value = M.UpdUnionCode;
                sqlParameter[22].Value = M.UpdBranchCode;
                sqlParameter[23].Value = M.UpdFuncId;

                db.ExecuteCmd(sql, sqlParameter);


            }
            catch (Exception e)
            {
                LogManager.error("MST_UnionPersister - updateMasterUnion " + e.Message);
            }
            finally
            {
                db.Close();
            }
        }

        /**
	    * 指定された値のデータモデルを取得する。
	    * @param unioncode 組合員コード
	    * @return MstUnion データモデル
	    */
        public MstUnion getDetail(string unioncode)
        {
            LogManager.info("MST_UnionPersister - getDetail");
            MstUnion model = null;
            SqlDB db = new SqlDB();
            try
            {
                StringBuilder sql = new StringBuilder();

                sql.Append("select ");
                sql.Append("union_code, ");
                //sql.Append( "branch_code, ");
                sql.Append("office_name, ");
                sql.Append("office_kana, ");
                sql.Append("office_zip_no, ");
                sql.Append("office_pref_code, ");
                sql.Append("office_city, ");
                sql.Append("office_address, ");
                sql.Append("office_tel, ");
                sql.Append("office_fax, ");
                sql.Append("office_email, ");
                sql.Append("daihyo_name, ");
                sql.Append("daihyo_kana, ");
                sql.Append("tanto_name, ");
                sql.Append("tanto_kana, ");
                sql.Append("trader_reg_no, ");
                sql.Append("user_clss, ");
                sql.Append("password, ");
                sql.Append("cancel_rest_flg, ");
                sql.Append("union_sts, ");
                sql.Append("reg_date, ");
                sql.Append("upd_date, ");
                sql.Append("upd_union_code, ");
                sql.Append("upd_branch_code, ");
                sql.Append("upd_func_id ");
                sql.Append("from mst_union ");
                sql.Append("where ");
                sql.Append("union_code = @union_code ");



                SqlParameter[] sqlParameter = { new SqlParameter("@union_code", SqlDbType.VarChar, 5) };
                sqlParameter[0].Value = unioncode;
                //sqlParameter[1].Value = 0;
                SqlDataReader dataReader = db.GetDataCmd(sql.ToString(), sqlParameter);

                if (dataReader.Read())
                {
                    model = new MstUnion();

                    MstUnionId MID = new MstUnionId();
                    model.Id = MID;

                    model.Id.UnionCode = (string)dataReader["union_code"];

                    //model.Id.BranchCode = (short)dataReader["branch_code"];

                    model.OfficeName = (string)dataReader["office_name"];

                    model.OfficeKana = (string)dataReader["office_kana"];

                    model.OfficeZipNo = (string)dataReader["office_zip_no"];

                    model.OfficePrefCode = (short)dataReader["office_pref_code"];

                    model.OfficeCity = (string)dataReader["office_city"];

                    model.OfficeAddress = (string)dataReader["office_address"];

                    model.OfficeTel = (string)dataReader["office_tel"];

                    model.OfficeFax = (string)dataReader["office_fax"];

                    model.OfficeEmail = (string)dataReader["office_email"];

                    model.DaihyoName = (string)dataReader["daihyo_name"];

                    model.DaihyoKana = (string)dataReader["daihyo_kana"];

                    model.TantoName = (string)dataReader["tanto_name"];

                    model.TantoKana = (string)dataReader["tanto_kana"];

                    model.TraderRegNo = (string)dataReader["trader_reg_no"];
                    model.TraderRegNo = model.TraderRegNo.Replace("-", "");

                    model.UserClss = (string)dataReader["user_clss"];

                    model.Password = (string)dataReader["password"];

                    model.CancelRestFlg = (string)dataReader["cancel_rest_flg"];

                    model.UnionSts = (string)dataReader["union_sts"];

                    model.RegDate = (DateTime)dataReader["reg_date"];

                    model.UpdDate = (DateTime)dataReader["upd_date"];

                    model.UpdUnionCode = (string)dataReader["upd_union_code"];

                    model.UpdBranchCode = (short)dataReader["upd_branch_code"];

                    model.UpdFuncId = (string)dataReader["upd_func_id"];
                }


            }
            catch
            {
                LogManager.error("MST_UnionPersister - getDetail");
            }
            finally
            {
                db.Close();

            }

            return model;
        }

        /**
	    * 指定された値のデータモデルを取得する。
	    * @param con コネクション
	    * @param union_code 組合員コード
	    * @return Vector BranchSearchModelのデータモデル
	    */
	    public ArrayList getByUnionCode( SqlDB db ,string union_code) {
		    ArrayList ret = new ArrayList();
		    BranchSearchModel model = null;

		    try {
			    //検索条件を生成
			    String pssql = "select " + "union_code, " + "branch_code, " + "office_name " + "from mst_union_view " + "where " + "union_sts != @union_sts " + "and union_code = @union_code " + "order by branch_code asc";

                SqlParameter[] sqlParameter = { new SqlParameter("@union_sts", SqlDbType.VarChar), new SqlParameter("@union_code", SqlDbType.VarChar) };
                sqlParameter[0].Value = MstUnion.STATUS_DELETE;
                sqlParameter[1].Value = union_code;

                SqlDataReader dataReader = db.GetDataCmd(pssql, sqlParameter);

			    while (dataReader.Read()) {
				    model = new BranchSearchModel();

				    model.Union_code = (string)dataReader["union_code"]; 
                    model.Branch_code = (short)dataReader["branch_code"];
				    model.Office_name = (string)dataReader["office_name"]; 
				   
				    ret.Add(model);
			    }

                dataReader.Close();

		    } catch (Exception e) {
                LogManager.error( "MST_UnionPersister "+e.Message+e.StackTrace );
			    
		    } 

		    return ret;

	    }

        /**
        * 組合員チェック
        * @return 存在しなければ:True
        */
        public bool isExistUnionCode(string unioncode) {
            int count = 0;
            SqlDB db = new SqlDB();
            try
            {
                string sql = "select count(*) from mst_union where union_code=@union_code";

                SqlParameter[] sqlParameter = { new SqlParameter("@union_code", SqlDbType.VarChar, 5) };
                sqlParameter[0].Value = unioncode;
                SqlDataReader dataReader = db.GetDataCmd(sql, sqlParameter);
                if (dataReader.Read())
                {
                    count = (int)dataReader[0];
                }


            }
            catch
            {
                LogManager.error("MST_UnionPersister - isExistUnionCode");
            }
            finally
            {
                db.Close();
            }
            return count>0;
        }

        public bool isExistUnionRegNo(string trader_reg_no)
        {
            int count = 0;
            SqlDB db = new SqlDB();
            try
            {

                string sql = "select count(*) from mst_union where trader_reg_no=@trader_reg_no";

                SqlParameter[] sqlParameter = { new SqlParameter("@trader_reg_no", SqlDbType.VarChar, 8) };
                sqlParameter[0].Value = trader_reg_no;
                SqlDataReader dataReader = db.GetDataCmd(sql, sqlParameter);
                if (dataReader.Read())
                {
                    count = (int)dataReader[0];
                }

            }
            catch
            {
                LogManager.error("MST_UnionPersister - isExistUnionRegNo");
            }
            finally
            {
                db.Close();
            }
            
            return count > 0;
        }

        public int getCount(string where)
        {
            int count = 0;
            SqlDB db = new SqlDB();
            try
            {

                string sql = "select count(*) from mst_union " + where;

                SqlParameter[] sqlParameter = { };
                SqlDataReader dataReader = db.GetDataCmd(sql, sqlParameter);
                if (dataReader.Read())
                {
                    count = (int)dataReader[0];
                }
                
            }
            catch
            {
                LogManager.error("MST_UnionPersister - getCount");
            }
            finally
            {
                db.Close();

            }

            return count;
        }

        /**
	     * 組合員本支店マスタを更新する
	     * @param con コネクション
	     * @param mst_union データモデル
	     * @return int 更新数
	     */
	    public void updateUnionCanselResetFlag(SqlDB db,MstUnion M)
        {
		    String pssql = "update mst_union set " + "cancel_rest_flg = @cancel_rest_flg, " + "upd_date = @upd_date, " + "upd_union_code = @upd_union_code, " + "upd_branch_code = @upd_branch_code, " + "upd_func_id = @upd_func_id " + "where " + "union_code = @union_code";

		    LogManager.info("MST_UnionPersister.class.getName() update union query : " + pssql);

		    try {
			   
                //SqlDB db = new SqlDB();
                SqlParameter[] sqlParameter = {   new SqlParameter("@cancel_rest_flg", SqlDbType.VarChar, 1),
                                                  new SqlParameter("@upd_date", SqlDbType.DateTime), 
                                                  new SqlParameter("@upd_union_code", SqlDbType.VarChar, 5),
                                                  new SqlParameter("@upd_branch_code", SqlDbType.SmallInt),
                                                  new SqlParameter("@union_code", SqlDbType.VarChar, 5)
                                                 
                                              };

                sqlParameter[0].Value = M.CancelRestFlg;
                sqlParameter[1].Value = M.UpdDate;
                sqlParameter[2].Value = M.UpdUnionCode;
                sqlParameter[3].Value = M.UpdBranchCode;
                sqlParameter[4].Value = M.Id.UnionCode;
                db.ExecuteCmd(pssql, sqlParameter);
                //db.Close();

		    } catch (Exception e) {

			    LogManager.error("MST_UnionPersister update_fail "+e.Message);
                throw new Exception("MST_UnionPersister update_fail " + e.Message);
			   
		    } 
	
	    }


        /**
         * //zhou--anyuan
         * 組合員コード一覧を取得する。。
         * @param con コネクション
         * @return Vector 報告率明細モデル入り
         */
        public List<TrnReportRate> getUnion_codeList(SqlDB con)
        {

            List<TrnReportRate> vct = new List<TrnReportRate>();
            TrnReportRate model = null;

            try
            {
                String sql = "select union_code from mst_union order by union_code";
                //SQL実行
                SqlDataReader rdr = con.GetDataCmd(sql, null);
                if (rdr != null)
                {
                    DataTable rs = DBUnit.ConvertDataReaderToDataTable(rdr);

                    foreach (DataRow row in rs.Rows)
                    {
                        model = new TrnReportRate();
                        model.Id = new TrnReportRateId();
                        model.Id.UnionCode = DBUnit.GetString(row["union_code"]);
                        vct.Add(model);
                    }
                }

            }
            catch (Exception e)
            {
                throw e;
            }
            //finally {
            //    try {
            //        rs.close();
            //    } catch (Exception e) {
            //        Logger.logWrite(MST_UnionPersister.class.getName(), "F", "getUnion_codeList - ResultSet.close", e);
            //    }
            //    try {
            //        stmt.close();
            //    } catch (Exception e) {
            //        Logger.logWrite(MST_UnionPersister.class.getName(), "F", "getUnion_codeList - PreparedStatement.close", e);
            //    }
            //}
            return vct;
        }

        /**zhou--anyuan
	    * 組合員コードのcancel_rest_flgを取得する。
	    * @param con コネクション
	    * @param mst_union データモデル
	    * @return int count
	    */
        public String getCancelflg(SqlDB con, String union_code)
        {
            String flg = "";
            try
            {
                String sql = "select cancel_rest_flg from mst_union where union_code = '" + union_code + "' ";
                //SQL実行
                SqlDataReader rdr = con.GetDataCmd(sql, null);
                if (rdr != null)
                {
                    DataTable rs = DBUnit.ConvertDataReaderToDataTable(rdr);


                    flg = DBUnit.GetString(rs.Rows[0]["cancel_rest_flg"]);
                }

            }
            catch (Exception e)
            {
                throw e;
            }
            return flg;
        }
    }
}
